package com.lx.dao.impl;

import com.lx.dao.UserDao;
import com.lx.entity.BankCard;
import com.lx.entity.User;
import com.lx.entity.vo.UserVo;
import com.lx.uitl.ConnUtil;
import com.lx.uitl.ConvertClassUtil;

import java.sql.*;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author c先生
 * @date 2022年05月05日 下午 4:05:07
 * @describe
 */

public class UserDaoImpl implements UserDao {
    @Override
    public Integer registerUser(User user) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql = "insert into users(user_name,user_pwd,user_status,user_phone,user_address,user_level,user_birthday,user_gender,open_account,id_card)\n" +
                "values(?,MD5(?),?,?,?,?,?,?,?,?);";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, user.getUserName());
        ps.setString(2, user.getUserPwd());
        ps.setInt(3, user.getUserStatus());
        ps.setString(4, user.getUserPhone());
        ps.setString(5, user.getUserAddress());
        ps.setInt(6, user.getUserLevel());
        ps.setDate(7, new java.sql.Date(user.getUserBirthday().getTime()));
        ps.setString(8, user.getUserGender());
        ps.setDate(9, new java.sql.Date(user.getOpenAccount().getTime()));
        ps.setString(10, user.getIdCard());
        Integer rs = ps.executeUpdate();
        ps.close();
        return rs;
    }


    @Override
    public String loginUser(String name) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql = "select * from users where user_name = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, name);
        ResultSet rs = ps.executeQuery();
        rs.next();
        String password = rs.getString("user_pwd");
        ps.close();
        return password;
    }

    @Override
    public Integer getByPhone(String userPhone) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        PreparedStatement ps = conn.prepareStatement("select * from users where user_phone = ?");
        ps.setString(1, userPhone);
        ResultSet rs = ps.executeQuery();
        rs.next();
        Integer id = rs.getInt("user_id");
        return id;
    }

    @Override
    public List<UserVo> getAll() throws SQLException {
        List<UserVo> list = new ArrayList<>();
        Connection conn = ConnUtil.getConnection();
        PreparedStatement ps = conn.prepareStatement("select u.*,b.* from users u join bank_card b on u.user_id = b.users_id;");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            User user = new User();
            user.setUserId(rs.getInt("user_id"));
            user.setUserName(rs.getString("user_name"));
            user.setUserPwd(rs.getString("user_pwd"));
            user.setUserAddress(rs.getString("user_address"));
            user.setUserBirthday(rs.getDate("user_birthday"));
            user.setUserGender(rs.getString("user_gender"));
            user.setUserLevel(rs.getInt("user_level"));
            user.setUserStatus(rs.getInt("user_status"));
            user.setUserPhone(rs.getString("user_phone"));
            user.setIdCard(rs.getString("id_card"));
            user.setOpenAccount(rs.getDate("open_account"));

            BankCard bankCard = new BankCard();
            bankCard.setCardId(rs.getString("card_id"));
            bankCard.setUsersId(rs.getInt("users_id"));
            bankCard.setCardPwd(rs.getString("card_pwd"));
            bankCard.setCardBalance(rs.getDouble("card_balance"));
            bankCard.setCardStatus(rs.getInt("card_status"));
            bankCard.setOpenCard(rs.getDate("open_card"));
            UserVo userVo = new UserVo(user,bankCard);
            list.add(userVo);
        }
        return list;
    }

    @Override
    public List<User> getUserInfoByName(String userName) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql = "select * from users where user_name = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, userName);
        ResultSet resultSet = ps.executeQuery();
        try {
            List<User> list = ConvertClassUtil.ConvertTo(User.class, resultSet);
            return list;
        } catch (InstantiationException | IllegalAccessException | ParseException e) {
            e.printStackTrace();
            System.out.println("类型转换异常");
            return null;
        } finally {
            resultSet.close();
        }

    }

    @Override
    public int getUserByPhoneNum(String phoneNum) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql = "select count(*) from users where user_phone=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, phoneNum);
        ResultSet resultSet = ps.executeQuery();
        resultSet.next();
        int result = resultSet.getInt(1);
        resultSet.close();
        return result;
    }

    @Override
    public User getUserByPhone(String phoneNum) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql = "select * from users where user_phone=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, phoneNum);
        ResultSet resultSet = ps.executeQuery();
        try {
            List<User> list = ConvertClassUtil.ConvertTo(User.class, resultSet);
            if (list.size() >= 1) {
                return list.get(0);
            } else {
                return null;
            }
        } catch (InstantiationException | IllegalAccessException | ParseException e) {
            e.printStackTrace();
            System.out.println("类型转换异常");
            return null;
        } finally {
            resultSet.close();
        }

    }

    @Override
    public int userCountByIdCard(String idCard) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql = "select * from users where id_card=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, idCard);
        ResultSet resultSet = ps.executeQuery();
        resultSet.next();
        int affectRows = resultSet.getInt(1);
        return affectRows;
    }

    @Override
    public int addUser(User user) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql="insert into users(user_name,user_pwd,user_phone,user_address,user_birthday,user_gender,open_account,id_card)VALUES(?,?,?,?,?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,user.getUserName());
        ps.setString(2,user.getUserPwd());
        ps.setString(3,user.getUserPhone());
        ps.setString(4,user.getUserAddress());
        ps.setDate(5,new java.sql.Date(user.getUserBirthday().getTime()));
        ps.setString(6,user.getUserGender());
        ps.setDate(7,new java.sql.Date(user.getOpenAccount().getTime()));
        ps.setString(8,user.getIdCard());
        int affectRows = ps.executeUpdate();
        return affectRows;
    }
}
